# %%
# 初始化
import zipfile

import pandas as pd

import reader
from common import *

(year, month), (year_new, month_new) = year_month()
year_month_list = last_n_month(year, month)

FP_GUANXIAN = f"data/0-管线/{year}{month:02}_管线数据.zip"
FP_HUGAN = f"data/0-互感/{year}{month:02}_互感数据.zip"
FP_ONU_GGL = (
    f"data/2-宽带装移机/{year}{month:02}_新装ONU管控报表清单.csv"
)
# FP_GONGDAN_DANGYUE = f"data/8-爱家TV/{year}{month:02}_爱家TV归档.zip"
FP_GONGDAN_LIST = [
    f"data/8-爱家TV/{year}{month:02}_爱家TV归档.zip"
    for year, month in year_month_list
]
FP_AJTV_JIHUO = (
    f"data/8-爱家TV/{year}{month:02}_爱家TV-激活数据.zip"
)
FP_KUANDAI_JIHE_PKL = (
    f"pickle/{year}{month:02}_宽带装移机工单地市稽核明细表.pkl"
)

check_files(
    FP_HUGAN,
    FP_GUANXIAN,
    FP_ONU_GGL,
    # FP_GONGDAN_DANGYUE,
    FP_GONGDAN_LIST,
    FP_KUANDAI_JIHE_PKL,
)

# %%
# 读取工单
usecols = (
    "包ID",
    "订单行号",
    "CRM订单号",
    "业务受理流水号",
    "资源类型",
    "接入方式",
    "客户类型",
    "五级地址ID",
    "宽带帐号",
    "电视账号",
    "派单日期",
    "派单人",
    "操作类型",
    "产品名称",
    "小区地址",
    "当前环节",
    "光功率达标情况",
    "归档时间",
    "工单状态",
    "装维组/用户班",
    "装维人员/用户班人员",
    "装维人员登录账号",
    "质检整改完成时间",
    "是否同装",
)
df = pd.concat(
    [
        pd.read_csv(
            f,
            usecols=usecols,
            compression="zip",
            encoding="gbk",
            dtype="string",
        )
        for f in FP_GONGDAN_LIST
    ],
    ignore_index=True,
)
df["派单日期"] = df["派单日期"].astype("datetime64")
df["归档时间"] = df["归档时间"].astype("datetime64")
df["宽带帐号"] = df["宽带帐号"].astype("string")
df["电视账号"] = df["电视账号"].astype("string")
df.rename(
    {"派单日期": "派单时间", "结单时间": "归档时间"},
    inplace=True,
    axis=1,
)

# %%
# 筛选数据
# 产品名称 = 爱家TV主电视基础产品
# 工单状态 = 归档
mask = (df.产品名称.str.find("爱家TV") != -1) & (df.工单状态 == "已归档")
df = df[mask]
df_dy = df[
    (df.归档时间 >= f"{year}-{month}-1")
    & (df.归档时间 < f"{year_new}-{month_new}-1")
]

df_dy_cj = df_dy[df_dy["操作类型"] == "业务拆除"]
df_dy_kt = df_dy[df_dy["操作类型"] == "业务开通"]
df_dy_yj = df_dy[df_dy["操作类型"] == "业务移机"]

df_dy = df_dy[df_dy["操作类型"].isin(["业务开通", "业务移机"])]


# %%
# 读取爱家TV电视激活数据
df_list = []
with zipfile.ZipFile(FP_AJTV_JIHUO) as zf:
    for name in zf.namelist():
        with zf.open(name) as f:
            df_tmp = pd.read_csv(
                f,
                usecols=["USERID", "USERSTATE"],
                dtype="string",
            )
            df_list.append(df_tmp)


df_jihuo = pd.concat(df_list, ignore_index=False)

# %%
# 90天同址同帐号先拆后装核查
df_tmp = df_dy_kt.merge(
    df.loc[
        df["操作类型"] == "业务拆除",
        [
            "电视账号",
            "小区地址",
            "派单时间",
        ],
    ],
    on=["电视账号", "小区地址"],
)

mask = df_dy["订单行号"].isin(
    df_tmp[
        df_tmp["派单时间_x"] - df_tmp["派单时间_y"]
        < pd.Timedelta(days=90)
    ]["订单行号"]
)
df_dy["90天同址同帐号先拆后装核查"] = S_PASSED
df_dy.loc[
    df_dy["操作类型"] != "业务开通", "90天同址同帐号先拆后装核查"
] = S_BYPASSED
df_dy.loc[mask, "90天同址同帐号先拆后装核查"] = S_NOT_PASSED

# %%
# 当月同帐号先装后拆核查
df_tmp = df_dy_kt.merge(
    df_dy_cj[["电视账号", "派单时间"]],
    on=["电视账号"],
)

mask = df_dy["订单行号"].isin(
    df_tmp[df_tmp["派单时间_x"] < df_tmp["派单时间_y"]]["订单行号"]
)

df_dy["当月同帐号先装后拆核查"] = S_PASSED
df_dy.loc[
    df_dy["操作类型"] != "业务开通", "当月同帐号先装后拆核查"
] = S_BYPASSED
df_dy.loc[mask, "当月同帐号先装后拆核查"] = S_NOT_PASSED

# %%
# 当月新增一址多户工单核查
df_guanxian = reader.read_csv(
    FP_GUANXIAN,
    columns=["宽带号码", "七级地址ID"],
)
guanxian_err_addr = df_guanxian.groupby("七级地址ID").filter(
    lambda x: len(x) >= 5
)["宽带号码"]
df_dy["当月新增一址多户工单核查"] = S_PASSED
df_dy.loc[
    df_dy["宽带帐号"].isin(guanxian_err_addr), "当月新增一址多户工单核查"
] = S_NOT_PASSED


# %%
# 登录端口异常工单核查
df_dy["登录端口异常工单核查"] = S_PASSED

df_hugan = reader.read_csv(
    FP_HUGAN,
    columns=(
        "宽带号码",
        "ONU下联端口",
        "所属ONU设备名称",
        "错误类型",
    ),
)
df_hugan_err_port = df_hugan.groupby(
    ["ONU下联端口", "所属ONU设备名称"]
).filter(lambda x: len(x) >= 2)["宽带号码"]
df_dy.loc[
    df_dy["宽带帐号"].isin(df_hugan_err_port), "登录端口异常工单核查"
] = S_NOT_PASSED


# %%
# 当月登录端口与资源系统不一致核查
df_dy["当月登录端口与资源系统不一致核查"] = S_NOT_PASSED
df_hugan_ok_ziyuan = df_hugan[df_hugan["错误类型"] == "正确资源"][
    "宽带号码"
]
df_dy.loc[
    df_dy["宽带帐号"].isin(df_hugan_ok_ziyuan),
    "当月登录端口与资源系统不一致核查",
] = S_PASSED


# %%
# 电视账号未激活核查
df_dy["电视账号未激活核查"] = S_NOT_PASSED
mask = df_dy.电视账号.isin(
    df_jihuo[df_jihuo.USERSTATE.isin(["1", "4"])].USERID
)
df_dy.loc[mask, "电视账号未激活核查"] = S_PASSED

# %%
# 服开工单规范性质检
df_dy["服开工单规范性质检"] = S_PASSED
df_dy.loc[df_dy.当前环节 == "整改", "服开工单规范性质检"] = S_NOT_PASSED

# %%
# 宽带账号重复核查
# 匹本月爱家TV工单，宽带帐号重复，且“产品名称“为 “爱家TV子电视基础产品”的工单，
# 不通过，余通过
df_dy["宽带账号重复核查"] = S_PASSED
tmp = df_dy.groupby("宽带帐号").filter(lambda x: len(x) > 1)
mask = df_dy.订单行号.isin(
    tmp.loc[(tmp.产品名称 == "爱家TV子电视基础产品"), "订单行号"]
)
df_dy.loc[mask, "宽带账号重复核查"] = S_NOT_PASSED

# %%
# 弱光核查
# usecols = (
#     "宽带账号",
#     "操作类型",
#     "产品类型",
#     "光功率是否达标",
#     "整改后光功率值",
# )
# df_ggl = pd.read_csv(
#     FP_ONU_GGL,
#     usecols=usecols,
#     encoding="gbk",
#     dtype="string",
# )
# df_ggl["整改后光功率值"] = df_ggl["整改后光功率值"].astype("float")

# ggl_ok = df_ggl.loc[
#     (df_ggl["产品类型"] == "家客开通")
#     & ((df_ggl["光功率是否达标"] == "是") | (df_ggl["整改后光功率值"] >= -25)),
#     "宽带账号",
# ]
# df_dy["弱光核查"] = S_NOT_PASSED
# df_dy.loc[
#     ("'" + df_dy["宽带帐号"]).isin(ggl_ok),
#     "弱光核查",
# ] = S_PASSED


# %%
# 宽带稽核状态核查
df_kuandai = pd.read_pickle(FP_KUANDAI_JIHE_PKL)
df_tmp = df_dy[["CRM订单号"]].merge(
    df_kuandai[["CRM业务流水号", "地市稽核结果"]],
    left_on="CRM订单号",
    right_on="CRM业务流水号",
    how="left",
)
df_dy["宽带稽核是否通过核查"] = S_PASSED
mask = df_dy["CRM订单号"].isin(
    df_tmp[df_tmp["地市稽核结果"] == S_NOT_PASSED]["CRM订单号"]
)
df_dy.loc[mask, "宽带稽核是否通过核查"] = S_NOT_PASSED

# %%
# 地市稽核结果
CHECK_COLUMNS = [
    "90天同址同帐号先拆后装核查",
    "当月同帐号先装后拆核查",
    "当月新增一址多户工单核查",
    "登录端口异常工单核查",
    "当月登录端口与资源系统不一致核查",
    "电视账号未激活核查",
    "服开工单规范性质检",
    "宽带账号重复核查",
    # "弱光核查",
    "宽带稽核是否通过核查",
]
not_passed = (df_dy[CHECK_COLUMNS] == S_NOT_PASSED).any(
    axis=1
)
df_dy["地市稽核结果"] = S_PASSED
df_dy.loc[not_passed, "地市稽核结果"] = S_NOT_PASSED

# %%
# 爱家TV开通地市稽核明细
df_dy.to_excel(
    f"report/8-爱家TV/{year}{month:02}_爱家TV开通地市稽核明细.xlsx",
    index=False,
)

# %%
# 完成
